package com.amazon.dao;

import com.amazon.bo.Product;

import com.amazon.bo.SMSCode;
import com.amazon.dao.base.BaseDao;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.query.Query;
import com.amazon.web.util.DateUtil;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate5.HibernateCallback;
import org.springframework.stereotype.Repository;
import java.math.BigInteger;
import java.util.*;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository
public class ProductDao extends BaseDao<Product> {
    public List<Product> getProductList(final int page, final int pageSize) {
         List<Product> results = execute(session -> (List<Product>) session
                 .createNativeQuery("SELECT * FROM product  ", Product.class)
                .setFirstResult((page - 1) * pageSize)
                 .setMaxResults(pageSize)
               .getResultList());
         return results != null ? results : new ArrayList<Product>();
    }

    public Product getProductBySku(String sku) {
        List<Product> results = execute(session -> (List<Product>) session
                .createNativeQuery("SELECT * FROM product where sku=:sku ", Product.class)
                .setParameter("sku",sku)
                .getResultList());
        return results.size()>0?results.get(0):null;
    }
    public Product getProductpBySku(final String SKU){
        Product user= execute(session -> session
                .createNativeQuery("SELECT * FROM product WHERE sku=:sku ", Product.class)
                .setParameter("sku", SKU)
                .uniqueResult());
        return  user;
    }




    //获取产品列表
    public List<Product> getProductListForDeal(String conn,final int page, final int pageSize){

        List<Product> results = execute(session -> (List<Product>) session
                .createNativeQuery("SELECT id,title,sku,description,item_package_quantity,msrp,package_dimensions FROM product"+conn)
                .setFirstResult((page - 1) * pageSize)
                .setMaxResults(pageSize)
                .getResultList()
        );

        return results != null ? results : new ArrayList<Product>();


    }

    public boolean productupdate(int goodsid,double amzonprice,String onlineday){
        try {
           Product oldProduct= getById(goodsid);
           if(oldProduct!=null){
               oldProduct.setOnlineday(onlineday);
               oldProduct.setAmzonprice(amzonprice);
               update(oldProduct);
               return true;
           }
        }
        catch (Exception ex)
        {return false;}
        return false;
    }
    //供应链产品列表
    public List<Product> getProductList(String conn,final int page, final int pageSize) {
        List<Product> results = execute(session -> (List<Product>) session
                .createNativeQuery("SELECT *  FROM product WHERE IFNULL(small_imgs,'')<>'' "
                        +conn+" ORDER BY updated_on desc ", Product.class)
                .setFirstResult((page - 1) * pageSize)
                .setMaxResults(pageSize)
                .getResultList());
        return results != null ? results : new ArrayList<Product>();
    }
    //更新产品状态
    public boolean productChangeStatus(int productid,int status){
        try {
            Product oldProduct= getProductById(productid);
            if(oldProduct!=null){
                oldProduct.setProductStatus(status);
                update(oldProduct);
                return true;
            }
        }
        catch (Exception ex)
        {return false;}
        return false;
    }
    //编辑图片
    public boolean  productChangeImg(int pid,String img,int status,String newColors){
        try {
            Product oldProduct= getProductById(pid);
            if(oldProduct!=null){
                oldProduct.setBigImg(img);
                oldProduct.setProps(newColors);
                oldProduct.setProductStatus(status);
                update(oldProduct);
                return true;
            }
        }
        catch (Exception ex)
        {return false;}
        return false;
    }

    public boolean productChangeAttr(Product p){
        try {
            Product oldProduct= getProductById((int)p.getId());
            if(oldProduct!=null){
                oldProduct.setMSRP(p.getMSRP());
                oldProduct.setBrand(p.getBrand());
                oldProduct.setDesigner(p.getDesigner());
                oldProduct.setCategoryName(p.getCategoryName());
                oldProduct.setPackageDimensions(p.getPackageDimensions());
                oldProduct.setNumberOfItems(p.getNumberOfItems());
                oldProduct.setShippingWeight(p.getShippingWeight());
                oldProduct.setProductStatus(p.getProductStatus());
                oldProduct.setOrigin(p.getOrigin());
                oldProduct.setCompanycode(p.getCompanycode());
                oldProduct.setShipfee(p.getShipfee());
                oldProduct.setHscode(p.getHscode());
                oldProduct.setHschinese(p.getHschinese());
                oldProduct.setDeclarePrice(p.getDeclarePrice());
                oldProduct.setPriceenglish(p.getPriceenglish());
                oldProduct.setForpeople(p.getForpeople());
                oldProduct.setPackageMaterial(p.getPackageMaterial());
                oldProduct.setProductMaterial(p.getProductMaterial());
                oldProduct.setStock(p.getStock());
                oldProduct.setBattery(p.getBattery());
                oldProduct.setProductClass(p.getProductClass());
                update(oldProduct);
                return true;
            }
        }
        catch (Exception ex)
        {return false;}
        return false;
    }

    public Product getProductById(int pid){
        Product p=execute(session->session.createNativeQuery("select * from product where id=:id",Product.class)
        .setParameter("id",pid).uniqueResult());
        return p;
    }

    public boolean productChangeDesc(int id,int status,String BulletPoint,String Description,String SearchTerms,String Title,String contry){
        try {
            Product oldProduct= getProductById(id);
            if(oldProduct!=null){
                switch (contry){
                    case "":{
                        oldProduct.setBulletPoint(BulletPoint);
                        oldProduct.setDescription(Description);
                        oldProduct.setSearchTerms(SearchTerms);
                        oldProduct.setTitle(Title);
                        break;
                    }
                    case "ch":{
                        oldProduct.setChBulletPoint(BulletPoint);
                        oldProduct.setChDescription(Description);
                        oldProduct.setChSearchTerms(SearchTerms);
                        oldProduct.setChTitle(Title);
                        break;
                    }
                    case "jp":{
                        oldProduct.setJpBulletPoint(BulletPoint);
                        oldProduct.setJpDescription(Description);
                        oldProduct.setJpSearchTerms(SearchTerms);
                        oldProduct.setJpTitle(Title);
                        break;
                    }
                    case "fr":{
                        oldProduct.setFrBulletPoint(BulletPoint);
                        oldProduct.setFrDescription(Description);
                        oldProduct.setFrSearchTerms(SearchTerms);
                        oldProduct.setFrTitle(Title);
                        break;
                    }
                    case "es":{
                        oldProduct.setEsBulletPoint(BulletPoint);
                        oldProduct.setEsDescription(Description);
                        oldProduct.setEsSearchTerms(SearchTerms);
                        oldProduct.setEsTitle(Title);
                        break;
                    }
                    case "de":{
                        oldProduct.setDeBulletPoint(BulletPoint);
                        oldProduct.setDeDescription(Description);
                        oldProduct.setDeSearchTerms(SearchTerms);
                        oldProduct.setDeTitle(Title);
                        break;
                    }
                    case "it":{
                        oldProduct.setItBulletPoint(BulletPoint);
                        oldProduct.setItDescription(Description);
                        oldProduct.setItSearchTerms(SearchTerms);
                        oldProduct.setItTitle(Title);
                        break;
                    }
                }
                update(oldProduct);
                return true;
            }
        }
        catch (Exception ex)
        {return false;}
        return false;
    }


    /** * 执行非查询sql * @param sql */

    public boolean updateChDesc(String item,String oriv,String repv) {


        String sql = "UPDATE product SET "+item+"=REPLACE("+item+",'"+oriv+"','"+repv+"') ";
        Session session=currentSession();
        try {
           // currentSession().createNativeQuery(sql);

            Query<?> query = session.createNativeQuery(sql);
            int updRow = query.executeUpdate();
           // int updRow = execute(session -> (int) session.createQuery(sql).executeUpdate());

            return updRow >= 0;
        }catch (Exception e) {
           throw e;
        }

    }
    //刊登撒谎那个品列表
    public Object getPublishList(int shopid,String  sku,String title,final int page, final int pageSize) {
        String sql=  "SELECT sg.id,p.id as productid,p.ch_title,p.`sku`,p.`big_img`,p.msrp,sg.updated_on,s.shopname,sgb.batch_status,u.real_name, p.msrp*s.`ratio` AS sellprice " +
                " FROM shop_goods sg LEFT JOIN shop s ON sg.shop_id=s.id " +
                " LEFT JOIN product p ON sg.product_id=p.id " +
                " LEFT JOIN shop_goods_batch sgb ON sg.batch_id=sgb.id " +
                " LEFT JOIN `user` u ON s.user_id=u.id " +
                " WHERE 1=1 "
                +(shopid!=0?" AND s.id="+shopid:"")
                +(sku!=""?" and p.sku like '%"+sku+"%' ":"")
                +(title!=""?" and p.ch_title like '%"+title+"%' ":"")
                + "order by  sg.updated_on desc limit "+((page-1)*pageSize)+","+pageSize;

        Object list= execute(session -> {
            Query query = session.createNativeQuery(sql);
            query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
            return query.getResultList();
        });
        return list;
    }
    //刊登商品数
    public int getPublicCount(int shopid,String  sku,String title){
        String sql=  "SELECT count(1) as num " +
                " FROM shop_goods sg LEFT JOIN shop s ON sg.shop_id=s.id " +
                " LEFT JOIN product p ON sg.product_id=p.id " +
                " LEFT JOIN shop_goods_batch sgb ON sg.batch_id=sgb.id " +
                " LEFT JOIN `user` u ON s.user_id=u.id " +
                " WHERE 1=1 "
                +(shopid!=0?" AND s.id="+shopid:"")
                +(sku!=""?" and p.sku like '%"+sku+"%' ":"")
                +(title!=""?" and p.ch_title like '%"+title+"%' ":"");


        Object result= execute(session -> {
            Query query = session.createNativeQuery(sql);
            query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
            return query.getSingleResult();
        });
        Map<String,Object> v=(Map<String,Object> )result;
        return Integer.parseInt(v.get("num").toString()) ;
    }



}

